Start of change

SYSFILES view

The SYSFILES view contains information about database files. Additional information is available in the QSYS2.SYSTABLES view.

The information returned is similar to the detail seen from the Display File Description (DSPFD) command and the Retrieve Database File Description (QDBRTVFD) API.

Authorization: The caller must have:
  • *EXECUTE authority to the library containing the file, and
  • *OBJOPR authority to the file.

The following table describes the columns in the view. The schema is QSYS2.

Table 1. SYSFILES view
Column Name System Column Name Data Type Description
SYSTEM_TABLE_SCHEMA LIB_NAME VARCHAR(10) The library containing the file.
SYSTEM_TABLE_NAME FILE_NAME VARCHAR(10) The name of the file.
TABLE_SCHEMA TABSCHEMA VARCHAR(128) The SQL schema name of the library.
TABLE_NAME TABNAME VARCHAR(128) The SQL name of the file.
IASP_NUMBER IASPNUMBER INTEGER The independent auxiliary storage pool (IASP) number.
TEXT_DESCRIPTION TEXT VARGRAPHIC(50) CCSID 1200
Nullable
The descriptive text for the file.

Contains the null value if there is no descriptive text.

NATIVE_TYPE DDS_TYPE VARCHAR(8) Type of file.
LOGICAL
This is a DDS created logical file or an SQL view or index.
PHYSICAL
This is a physical file or an SQL table.
FILE_TYPE FILETYPE VARCHAR(6) Type of file.
DATA
This is a data file.
SOURCE
This is a source physical file.
SQL_OBJECT_TYPE SQL_TYPE VARCHAR(5)
Nullable
SQL object type.
INDEX
This is an SQL index.
TABLE
This is an SQL table.
VIEW
This is an SQL view.

Contains the null value if this is not an SQL object.

LAST_ALTERED_TIMESTAMP ALTEREDTS TIMESTAMP(0) Timestamp when the file was last altered or created.
FILE_LEVEL_ID FILE_LVLID CHAR(13) File level identifier. This is the date of the file in CYYMMDDHHMMSS format.
LEVEL_CHECK LVLCHK VARCHAR(3) Record format level check (LVLCHK).
NO
The record format level identifiers are not checked when the file is opened.
YES
The record format level identifiers are checked when the file is opened.
See Level checking for some additional information about how level checking works.
FILE_OWNER OWNER VARCHAR(10)
Nullable
The owner of the object.

Contains the null value if the object owner is not available.

CREATE_PUBLIC_AUTHORITY PUB_AUTH VARCHAR(10) The public authority that the file was created with (AUT). This is not the current public authority for the file.
*ALL
Public all authority
*CHANGE
Public change authority
*EXCLUDE
Public exclude authority
*USE
Public use authority
authorization-list-name
The name of the authorization list whose authority is used for the file.
NUMBER_MEMBERS MEMBERS INTEGER Number of members.
MAXIMUM_ MEMBERS MAXMBRS INTEGER
Nullable
Maximum members (MAXMBRS).
1 to 32767
The maximum number of members for the file.

Contains the null value if no maximum is specified; 32767 is used (*NOMAX).

MAXIMUM_RECORD_LENGTH RECLENGTH INTEGER Maximum record length. This is the length of the record in the file's record format that contains the largest number of bytes.
NUMBER_KEY_FIELDS KEY_FLDS INTEGER Number of key fields for the file.
MAXIMUM_KEY_LENGTH KEY_LEN INTEGER Maximum key length for the file.
TRIGGER_COUNT TRIG_CNT INTEGER Number of triggers.
CONSTRAINT_COUNT CONSTR_CNT INTEGER
Nullable
Number of constraints for the file.

Contains the null value if this is not a physical file.

NUMBER_BASED_ON_FILES BASE_FILES INTEGER
Nullable
Number of files directly referenced by a logical file, view, or index.

Contains the null value for physical files.

Start of changeBASED_ON_FILESEnd of change Start of changeBASED_ONEnd of change Start of changeCLOB(35K) CCSID 1208
Nullable
End of change
Start of changeA list of files directly referenced by this logical file, view, or index.
This list is returned as an array within a JSON object. The array is identified by BASED_ON_FILES. Each entry in the JSON array identifies one file. Each array entry can contain information with the following keys:
  • LIBRARY
  • FILE
  • MEMBER
  • LF_FORMAT

Contains the null value for physical files.

End of change
ALLOW_READ ALWREAD VARCHAR(3) Allow read operation.
NO
Records are not allowed to be read from the file.
YES
Records are allowed to be read from the file.
ALLOW_WRITE ALWWRITE VARCHAR(3) Allow write operation.
NO
Records are not allowed to be written to the file.
YES
Records are allowed to be written to the file.
ALLOW_UPDATE ALWUPD VARCHAR(3) Allow update operation (ALWUPD).
NO
Records are not allowed to be updated in the file.
YES
Records are allowed to be updated in the file.
ALLOW_DELETE ALWDLT VARCHAR(3) Allow delete operation (ALWDLT).
NO
Records are not allowed to be deleted from the file.
YES
Records are allowed to be deleted from the file.
MAXIMUM_FILE_WAIT_TIME WAITFILE INTEGER Maximum file wait time (WAITFILE).
-1
The default wait time specified in the class description is used (*CLS).
0
The program does not wait for the file; an immediate allocation is required (*IMMED).
1 through 32767
The maximum number of seconds a program waits for the file.
MAXIMUM_RECORD_WAIT_TIME WAITRCD INTEGER Maximum record wait time (WAITRCD).
-2
The default wait time allowed by the system is used. This is 32767 seconds (*NOMAX).
-1
The program does not wait for the record, an immediate allocation is required (*IMMED).
1 through 32767
The maximum number of seconds a program waits for the record.
FORCE_WRITE_RATIO FRCRATIO INTEGER
Nullable
Records to force a write (FRCRATIO).
1 through 32767
When the cumulative count of inserted, updated, and deleted records has reached this value, the changed records are forced to storage.

Contains the null value is there is no force write ratio.

SELECT_OMIT SELECTOMIT VARCHAR(3) Select/omit setting.
NO
The file is not a select/omit logical file.
YES
The file is a select/omit logical file.
PROGRAM_DESCRIBED PGM_DESC VARCHAR(3) Program described file setting.
NO
The file is not program described.
YES
The file is program described.
DISTRIBUTED DIST_FILE VARCHAR(3) Distributed file setting.
NO
The file is not distributed.
YES
The file is distributed.
FILE_VRM FILE_VRM CHAR(6) File version, release, and modification level. VxRyMz, where x is the version, y the release, and z the modification level. This is the release where the file was created.
EARLIEST_POSSIBLE_RELEASE MINRLS CHAR(6)
Nullable
Earliest supported version, release, and modification level. New database support used in the file will prevent the file from being saved to a prior version, release, and modification level. The value is formatted as VxRyMz, where x is the version, y the release, and z the modification level.

Contains the null value if the value is prior to V2.

ALLOW_NULL_KEYS ALWNULLK VARCHAR(3)
Nullable
Allow null value key setting (ALWNULL).
NO
Null value keys are not allowed.
YES
Null value keys are allowed.

Contains the null value if ACCESS_PATH_KEYED is NO.

ALLOW_NULL_DATA ALWNULLD VARCHAR(3) Allow null value data (ALWNULL).
NO
The file record format(s) do not allow null value fields.
YES
The file record format(s) allow null value fields.
PRIMARY_KEY PRIKEY VARCHAR(3) Primary key (*PRIKEY).
NO
The access path for the file is not a primary key.
YES
The access path for the file is a primary key.
UNIQUE_CONSTRAINT UNQCST VARCHAR(3) Unique constraint.
NO
The access path for the file is not a unique constraint.
YES
The access path for the file is a unique constraint.
VOLATILE VOLATILE VARCHAR(3) SQL volatile table setting.
NO
The file is not an SQL volatile table.
YES
The file is an SQL volatile table.
KEEP_IN_MEMORY KEEPINMEM VARCHAR(3) The memory preference of the file.
NO
The file does not have the keep in memory indication set.
YES
The file has the keep in memory indication set.
MEDIA_PREFERENCE UNIT VARCHAR(4) Preferred storage unit for the file (UNIT).
*ANY
No storage media is preferred. Storage will be allocated from any available storage media.
*SSD
Solid state disk storage media is preferred. Storage may be allocated from solid state disk storage media, if available.
SOURCE_FILE_LIBRARY SRCLIB VARCHAR(10)
Nullable
Library containing SOURCE_FILE.

Contains the null value if a source file was not used.

SOURCE_FILE SRCFILE VARCHAR(10)
Nullable
Name of the source file containing the DDS used to create the file.

Contains the null value if a source file was not used.

SOURCE_FILE_MEMBER SRCMBR VARCHAR(10)
Nullable
Source file member name within SOURCE_FILE used to create the file.

Contains the null value if a source file was not used.

ACCESS_PATH_KEYED ACCESSPATH VARCHAR(3) Whether the file has a keyed sequence access path.
NO
The file does not have a keyed sequence access path. The file is processed using arrival sequence.
YES
The file has a keyed sequence access path.
ACCESS_PATH_TYPE AP_TYPE VARCHAR(14)
Nullable
Access path type.
EVI
Encoded vector with a 1-, 2-, or 4-byte vector.
KEYED FCFO
Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in first-changed-first-out (FCFO) order.
KEYED FIFO
Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in first-in-first-out (FIFO) order.
KEYED LIFO
Keyed sequence access path with duplicate keys allowed. Duplicate keys are accessed in last-in-first-out (LIFO) order.
KEYED NO ORDER
Keyed sequence access path with duplicate keys allowed. No order is guaranteed when accessing duplicate keys.
KEYED UNIQUE
Keyed sequence access path with no duplicate keys allowed (UNIQUE).

Contains the null value if ACCESS_PATH_KEYED is NO.

ACCESS_PATH_MAINTENANCE MAINT VARCHAR(6)
Nullable
Access path maintenance (MAINT).
*DLY
Delayed maintenance
*IMMED
Immediate maintenance
*REBLD
Rebuild maintenance

Contains the null value if ACCESS_PATH_KEYED is NO.

ACCESS_PATH_SIZE ACCPTHSIZ VARCHAR(7)
Nullable
Access path size (ACCPTHSIZ).
*MAX1TB
All access paths associated with this file will be allowed to occupy a maximum of 1 terabyte (1 099 511 627 776 bytes) of auxiliary storage
*MAX4GB
All access paths associated with this file will be allowed to occupy a maximum of 4 gigabytes (4 294 966 272 bytes) of auxiliary storage.

Contains the null value if ACCESS_PATH_KEYED is NO.

LOGICAL_PAGE_SIZE PAGESIZE INTEGER
Nullable
Access path page size.
-1
Access path is a 4 gigabyte access path
0
System determines page size from the key length of the access path
8
Page size is 8 kilobytes
16
Page size is 16 kilobytes
32
Page size is 32 kilobytes
64
Page size is 64 kilobytes
128
Page size is 128 kilobytes
256
Page size is 256 kilobytes
512
Page size is 512 kilobytes

Contains the null value if ACCESS_PATH_KEYED is NO.

FORCE_KEYED_ACCESS_PATH FRCACCPTH VARCHAR(3)
Nullable
Force keyed access path (FRCACCPTH).
NO
The access path and changed records are not forced to auxiliary storage when the access path is changed.
YES
The access path and changed records are forced to auxiliary storage when the access path is changed (*YES).

Contains the null value if ACCESS_PATH_KEYED is NO.

ACCESS_PATH_JOURNALED JOURNALED VARCHAR(3)
Nullable
Access path journaled.
NO
The access path is not journaled.
YES
The access path is journaled.

Contains the null value if ACCESS_PATH_KEYED is NO.

ACCESS_PATH_RECOVERY RECOVER VARCHAR(7)
Nullable
Access path recovery (RECOVER).
*AFTIPL
The file access path is built after the IPL is completed.
*IPL
The file access path is built during the IPL.
*NO
The file access path is built when the file is next opened.

Contains the null value if ACCESS_PATH_KEYED is NO.

SRTSEQ_IND SRTSEQ_IND INTEGER Sort sequence table (SRTSEQ) indicators.
1
No sort sequence table is used for the file, and the hexadecimal value of the characters will be used to determine the sort sequence (*HEX).
2
A sort sequence table was specified for the file.
3
No sort sequence table for the file; however, an alternate collating sequence table was specified.
SORT_SEQUENCE_LIBRARY SRTSEQ_LIB VARCHAR(10)
Nullable
The library containing the sort sequence table or alternate collating sequence table. Can contain the special value *LIBL.

Contains the null value if SRTSEQ_IND is 1 or if there is no library.

SORT_SEQUENCE SRTSEQ VARCHAR(10)
Nullable
The sort sequence table or alternate collating sequence table associated with the file.

Contains the null value if SRTSEQ_IND is 1.

LANGUAGE_IDENTIFIER LANGID CHAR(3)
Nullable
Language identifier (LANGID).

Contains the null value if there is no language identifier.

ROUNDING_MODE DECFLTRND VARCHAR(8)
Nullable
Rounding mode to be used for decimal floating point (DECFLOAT) calculations.
CEILING
DOWN
FLOOR
HALFDOWN
HALFEVEN
HALFUP
UP

Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file.

DECFLOAT_WARNINGS DECFLTWRN VARCHAR(3)
Nullable
Indicates whether warnings should be returned from decimal floating point calculations.
NO
Warnings are not returned.
YES
Warnings are returned.

Contains the null value if the file has no decimal floating point fields or this is not an SQL view, SQL index with a derived key expression, SQL materialized query table, or logical file.

NUMBER_RECORD_FORMATS FORMATS INTEGER Total number of record formats.
FORMAT_LEVEL_ID FMTLVLID CHAR(13)
Nullable
The record format level ID.

Contains the null value if file has more than one format or if no value is available.

FORMAT_NAME FMT_NAME VARCHAR(10)
Nullable
The name of the record format.

Contains the null value if file has more than one format.

RECORD_LENGTH RCD_LEN INTEGER
Nullable
The length of the record format.

Contains the null value if file has more than one format.

NUMBER_FIELDS FIELDS INTEGER
Nullable
The number of fields in the record format.

Contains the null value if file has more than one format.

COMMON_CCSID CCSID INTEGER
Nullable
The CCSID used when all fields with a character, open, and graphic data type use the same CCSID.

Contains the null value if file has more than one format or if all character, open, and graphic fields do not use the same CCSID.

CONTAINS_EXPLICIT_CCSID EXPLICIT VARCHAR(3)
Nullable
Explicit CCSID setting.
NO
A CCSID was not specified for any character type fields in the format.
YES
A CCSID was specified for one or more character type fields in the format.

Contains the null value if file has more than one format.

CONTAINS_MULTIPLE_CCSIDS MULTIPLE VARCHAR(3) Multiple CCSID setting.
NO
The file has only one CCSID for its character type fields or the file has no character type fields.
YES
The file has more than one CCSID for its character type fields
CONTAINS_UNICODE UNICODE VARCHAR(3)
Nullable
Format contains UTF-8, UTF-16, or UCS-2 fields.
NO
The file record format does not contain UTF-8, UTF-16, or UCS-2 fields.
YES
The file record format contains UTF-8, UTF-16, or UCS-2 fields.

Contains the null value if file has more than one format.

CONTAINS_VARYING_LENGTH VARLEN VARCHAR(3) File contains variable length fields (VARLEN).
NO
The file record format does not contain variable length fields.
YES
The file record format contains variable length fields.
CONTAINS_DATETIME DATETIME VARCHAR(3) File contains date/time/timestamp fields.
NO
The file record format does not contain date, time, or timestamp fields.
YES
The file record format contains date, time, or timestamp fields.
CONTAINS_GRAPHIC GRAPHIC VARCHAR(3) File contains graphic fields.
NO
The file record format does not contain graphic fields.
YES
The file record format contains graphic fields.
CONTAINS_LOB LOB VARCHAR(3) File contains large object fields. These are the SQL data types character large object (CLOB), double-byte character large object (DBCLOB), and binary large object (BLOB).
NO
The file record format does not have a large object field.
YES
The file record format has a large object field.
CONTAINS_ROWID ROWID VARCHAR(3) File contains ROWID fields.
NO
The file record format does not have a ROWID column.
YES
The file record format has a ROWID column.
CONTAINS_UDT UDT VARCHAR(3) File contains user-defined type fields.
NO
The file record format does not have a user-defined type field.
YES
The file record format has a user-defined type field.
CONTAINS_DATALINK DATALINK VARCHAR(3) File contains DataLink fields.
NO
The file record format does not have a DataLink field.
YES
The file record format has a DataLink field.
CONTAINS_DATALINK_
FILE_LINK_CONTROL
DATALINKFL VARCHAR(3) File contains DataLink with FILE LINK CONTROL fields.
NO
The file record format does not have a DataLink field with FILE LINK CONTROL.
YES
The file record format has a DataLink field with FILE LINK CONTROL.
CONTAINS_NULL NULLABLE VARCHAR(3)
Nullable
File contains null capable fields.
NO
The file record format does not have any null capable fields.
YES
The file record format has null capable fields.

Contains the null value if file has more than one format.

CONTAINS_DEFAULT DFT VARCHAR(3)
Nullable
The physical file format contains fields with explicit default values.
NO
The file record format does not have any fields with explicit default values.
YES
The file record format has fields with explicit default values.

Contains the null value if file has more than one format.

CONTAINS_IDENTITY IDENTITY VARCHAR(3) File contains an identity column.
NO
The file record format does not have an identity column.
YES
The file record format has an identity column.
CONTAINS_ROW_CHANGE_
TIMESTAMP
ROW_CHANGE VARCHAR(3) File contains a row change timestamp column.
NO
The file record format does not have a row change timestamp column.
YES
The file record format has a row change timestamp column.
CONTAINS_USER_DEFINED_
FUNCTION
UDF VARCHAR(3) File contains a user-defined function.
NO
The file does not use a user-defined function.
YES
The file uses a user-defined function.
Values for the following columns are returned when NATIVE_TYPE is PHYSICAL. Otherwise, the columns will contain the null value.
ALLOCATE_STORAGE ALLOCATE VARCHAR(3)
Nullable
The allocate storage setting (ALLOCATE).
NO
New members added to the file allow the system to determine storage space that is allocated for the member (ALLOCATE(*NO))
YES
New members added to the file use the initial number of records to determine storage space that is allocated for the member (ALLOCATE(*YES)).
CONTIGUOUS_STORAGE CONTIG VARCHAR(3)
Nullable
The contiguous storage setting (CONTIG).
NO
Storage should not be attempted to be allocated contiguously (CONTIG(*NO)).
YES
Storage should be attempted to be allocated contiguously (CONTIG(*YES)).

Contains the null value if ALLOCATE_STORAGE is NO.

MAXIMUM_DELETED_PERCENTAGE DLTPCT INTEGER
Nullable
Maximum percentage of deleted records allowed (DLTPCT).
1 to 100
The threshold percentage of deleted records a member can contain before a message is sent to the history log.
See Deleted records for additional information.

Contains the null value if the number of deleted records is not checked when the member is closed (*NONE).

INITIAL_RECORDS SIZE_INIT INTEGER
Nullable
Initial number of records (SIZE). This is the number of records that can be inserted before an automatic extension occurs.

Contains the null value if the number of records that can be inserted into each member is not limited by the user. The system determines the maximum member size (*NOMAX).

INCREMENT_RECORDS SIZE_INCR INTEGER
Nullable
Increment number of records (SIZE). This is the maximum number of records that can be inserted into the member after an automatic extension occurs.

Contains the null value when INITIAL_RECORDS is null.

MAXIMUM_INCREMENTS SIZE_MAX INTEGER
Nullable
Maximum number of increments (SIZE). This is the maximum number of automatic extensions that can be made to the member.

Contains the null value when INITIAL_RECORDS is 0.

REUSE_DELETED_RECORDS REUSEDLT VARCHAR(3)
Nullable
Reuse deleted records (REUSEDLT).
NO
Deleted records are not reused on subsequent writes or inserts.
YES
Deleted records can be reused on subsequent writes or inserts.
MATERIALIZED_QUERY_TABLE MQT VARCHAR(3)
Nullable
SQL materialized query table setting.
NO
This is not an SQL materialized query table.
YES
This is an SQL materialized query table.
PARTITIONED_TABLE PARTITION VARCHAR(3)
Nullable
Partitioned table setting.
NO
This is not a partitioned table.
YES
This is a partitioned table.
ROW_AND_COLUMN_ACCESS_
CONTROL
RCAC VARCHAR(3)
Nullable
Row and column access control setting.
NO
Access controls are not defined for the file.
YES
Access controls are defined for the file.
The QSYS2.SYSCONTROLS view contains detailed information about row and column access controls.
Values for the following columns are returned when NATIVE_TYPE is LOGICAL. Otherwise, the columns will contain the null value.
TOTAL_SELECT_OMIT TOTAL_SO INTEGER
Nullable
Total number of select/omit statements for all record formats.
FMTSLR_LIBRARY FMTSLR_LIB VARCHAR(10)
Nullable
Record format selector program library (FMTSLR)

Contains the null value if there is no record format selector program.

FMTSLR_PROGRAM FMTSLR_PGM VARCHAR(10)
Nullable
Record format selector program (FMTSLR)

Contains the null value if there is no record format selector program.

IS_JOIN_LOGICAL JFILE VARCHAR(3)
Nullable
Join logical file setting (JFILE).
NO
The file is not a join logical file.
YES
The file is a join logical file.
DYNAMIC_SELECTION DYNSLT VARCHAR(3)
Nullable
Dynamic selection setting (DYNSLT).
NO
The selection and omission tests specified for the file are done when the access path is updated.
YES
The selection and omission tests specified for the file are done when the file is read.
WITH_CHECK_OPTION CHECK VARCHAR(8)
Nullable
With check option.
CASCADED
The cascaded check option was specified.
NO
No check option was specified or this is not an SQL view.
LOCAL
The local check option was specified.
PHYSICAL_LOB PF_LOB VARCHAR(3)
Nullable
Whether this logical file has no large object fields, but the based-on physical file has a large object field.
NO
The logical file and based-on physical file either both have large object fields or both do not.
YES
The logical file has no large object fields, but the based-on physical file has a large object field.
PHYSICAL_DATALINK PF_DATALNK VARCHAR(3)
Nullable
Whether this logical file has no DataLink fields, but the based-on physical file has a DataLink field.
NO
The logical file and based-on physical file either both have DataLink fields or both do not.
YES
The logical file has no DataLink fields, but the based-on physical file has a DataLink field.
INDEX_COLUMN_IS_EXPRESSION IXEXP VARCHAR(3)
Nullable
If the SQL index key column is an expression.
NO
The key column is not an expression or this is not an SQL index.
YES
The key column is an expression.
INDEX_EXPRESSION_HAS_UDF IXEXPUDF VARCHAR(3)
Nullable
If the SQL index key column is an expression and the expression contains a user-defined function (UDF).
NO
The key column is not an expression or the expression does not contain a user-defined function, or this is not an SQL index.
YES
The key column is an expression and the expression contains a UDF.
INDEX_HAS_SEARCH_CONDITION SPARSE VARCHAR(3)
Nullable
If the index has a search condition.
NO
The index does not have a search condition, or this is not an SQL index
YES
The index has a search condition.
INDEX_SEARCH_CONDITION_
HAS_UDF
SPARSE_UDF VARCHAR(3)
Nullable
If the index search condition contains a user-defined function.
NO
The index is not sparse or does not contain a user-defined function, or this is not an SQL index
YES
The index is sparse and the search condition contains a UDF.

Examples

  • Examine all the files in APPLIB1.
     SELECT * FROM QSYS2.SYSFILES WHERE LIB_NAME = 'APPLIB1';
  • List the files that native logical files in APPLIB1 are based on.
    WITH BASED_ONS (LF_NAME, BASED_ON_NAMES) AS (
        SELECT TABLE_NAME, BASED_ON_FILES
          FROM QSYS2.SYSFILES 
          WHERE SYSTEM_TABLE_SCHEMA = 'APPLIB1' AND
                NATIVE_TYPE = 'LOGICAL' AND
                SQL_OBJECT_TYPE IS NULL  
      )
      SELECT DISTINCT LF_NAME, LIBRARY AS BASED_ON_LIBRARY, FILE AS BASED_ON_FILE 
        FROM BASED_ONS, JSON_TABLE(
               BASED_ON_NAMES,
               'lax $.BASED_ON_FILES'
               COLUMNS(
                 LIBRARY CHAR(10), FILE CHAR(10)
               ))
      order by 1, 2, 3;
     
End of change